//
// Created by twetec on 25-6-21.
//

#include <network_prober/database/memory_db_holder.h>

#include <boost/format.hpp>

static const std::string_view kProbeRequestHandlePlanTableName = "probe_request_handle_plan";

namespace network_prober::database
{
enum class ObservedTargetTableColIdx : std::uint8_t
{
    id = 0,
    uuid,
    interface,
    address,
    port,
    protocol,
    responseCode,
    ignoreTsl,
    requestMethod,
    requestHeader,
    requestBody,
    responseChallenge,
    userName,
    password,
    databaseName,
    queryStatement,
    matchCondition,
    keyWords
};

enum class ProbeTaskTableColIdx : std::uint8_t
{
    id = 0,
    uuid,
    interface,
    address,
    port,
    protocol,
    responseCode,
    ignoreTsl,
    requestMethod,
    requestHeader,
    requestBody,
    responseChallenge,
    userName,
    password,
    databaseName,
    queryStatement,
    matchCondition,
    keyWords
};

enum class DeviceTableColIdx : std::uint8_t
{
    id = 0,
    uuid,
    interface,
    address,
    backupDate,
    command,
    protocol,
    account,
    password,
    privilegedPassword,
    port
};

enum class BackupTaskTableColIdx : std::uint8_t
{
    id = 0,
    uuid,
    interface,
    address,
    backupDate,
    command,
    protocol,
    account,
    password,
    privilegedPassword,
    port
};

enum class BackupTaskResultTableColIdx : std::uint8_t
{
    id = 0,
    uuid,
    backupData,
    backupDate
};

void MemoryDBHolder::init()
{
    // 初始化观测目标表
    _initObservedTargetTable();
    // 初始化备份目标表
    _initDeviceTable();
    // 初始化观测任务表
    _initProbeTaskTable();
    // 初始化备份任务表
    _initBackupTaskTable();
    // 初始化备份内容标
    _initBackupTaskResultTable();
}

void MemoryDBHolder::_initBackupTaskResultTable()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    SPDLOG_INFO("为 t_backup_task_result 表重置自增主键序列...");

    // 查询结果
    duckdb_result queryResult;

    std::string dropPrimaryKeySequenceSQL("DROP SEQUENCE IF EXISTS seq_backup_task_result_id;");
    ret = duckdb_query(conn, dropPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 自增主键序列 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("为 t_backup_task_result 表创建自增主键序列");

    std::string createPrimaryKeySequenceSQL("CREATE SEQUENCE seq_backup_task_result_id START 1;");
    ret = duckdb_query(conn, createPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 自增主键序列 创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("重置 t_backup_task_result 表..");

    std::string dropTableSQL("DROP TABLE IF EXISTS t_backup_task_result;");
    ret = duckdb_query(conn, dropTableSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    // 执行SQL,创建表
    SPDLOG_INFO("初始化 t_backup_task_result 表..");

    std::stringstream createTableSQL;
    createTableSQL << "CREATE TABLE t_backup_task_result";
    createTableSQL << "(";
    createTableSQL << "id INTEGER PRIMARY KEY DEFAULT nextval('seq_backup_task_result_id')" << ",";
    createTableSQL << "uuid VARCHAR(255)" << ",";
    createTableSQL << "backupData TEXT" << ",";
    createTableSQL << "backupDate INTEGER";
    createTableSQL << ");";

    ret = duckdb_query(conn, createTableSQL.str().c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_backup_task_result 表初始化完成..");
}

void MemoryDBHolder::   _initProbeTaskTable()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    SPDLOG_INFO("为 t_probe_task 表重置自增主键序列...");

    // 查询结果
    duckdb_result queryResult;

    std::string dropPrimaryKeySequenceSQL("DROP SEQUENCE IF EXISTS seq_probe_task_id;");
    ret = duckdb_query(conn, dropPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_probe_task 表 自增主键序列 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("为 t_probe_task 表创建自增主键序列");

    std::string createPrimaryKeySequenceSQL("CREATE SEQUENCE seq_probe_task_id START 1;");
    ret = duckdb_query(conn, createPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_probe_task 表 自增主键序列 创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("重置 t_probe_task 表..");

    std::string dropTableSQL("DROP TABLE IF EXISTS t_probe_task;");
    ret = duckdb_query(conn, dropTableSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_probe_task 表 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    // 执行SQL,创建表
    SPDLOG_INFO("初始化 t_probe_task 表..");

    std::stringstream createTableSQL;
    createTableSQL << "CREATE TABLE t_probe_task";
    createTableSQL << "(";
    createTableSQL << "id INTEGER PRIMARY KEY DEFAULT nextval('seq_probe_task_id')" << ",";
    createTableSQL << "uuid VARCHAR(255)" << ",";
    createTableSQL << "interface VARCHAR(255)" << ",";
    createTableSQL << "address VARCHAR(255)" << ",";
    createTableSQL << "port INTEGER" << ",";
    createTableSQL << "protocol INTEGER,";
    createTableSQL << "responseCode VARCHAR(255)" << ",";
    createTableSQL << "ignoreTsl INTEGER" << ",";
    createTableSQL << "requestMethod VARCHAR(255)" << ",";
    createTableSQL << "requestHeader VARCHAR(255)" << ",";
    createTableSQL << "requestBody VARCHAR(255)" << ",";
    createTableSQL << "responseChallenge BOOLEAN" << ",";
    createTableSQL << "userName VARCHAR(255)" << ",";
    createTableSQL << "password VARCHAR(255)" << ",";
    createTableSQL << "databaseName VARCHAR(255)" << ",";
    createTableSQL << "queryStatement VARCHAR(255)" << ",";
    createTableSQL << "matchCondition BOOLEAN" << ",";
    createTableSQL << "keyWords VARCHAR(255)";
    createTableSQL << ");";

    ret = duckdb_query(conn, createTableSQL.str().c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_probe_task 表创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_probe_task 表初始化完成..");
}

void MemoryDBHolder::_initBackupTaskTable()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    SPDLOG_INFO("为 t_backup_task 表重置自增主键序列...");

    // 查询结果
    duckdb_result queryResult;

    std::string dropPrimaryKeySequenceSQL("DROP SEQUENCE IF EXISTS seq_backup_task_id;");
    ret = duckdb_query(conn, dropPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task 表 自增主键序列 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("为 t_backup_task 表创建自增主键序列");

    std::string createPrimaryKeySequenceSQL("CREATE SEQUENCE seq_backup_task_id START 1;");
    ret = duckdb_query(conn, createPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task 表 自增主键序列 创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("重置 t_backup_task 表..");

    std::string dropTableSQL("DROP TABLE IF EXISTS t_backup_task;");
    ret = duckdb_query(conn, dropTableSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task 表 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    // 执行SQL,创建表
    SPDLOG_INFO("初始化 t_backup_task 表..");

    std::stringstream createTableSQL;
    createTableSQL << "CREATE TABLE t_backup_task";
    createTableSQL << "(";
    createTableSQL << "id INTEGER PRIMARY KEY DEFAULT NEXTVAL('seq_backup_task_id'),";
    createTableSQL << "uuid VARCHAR(255)" << ",";
    createTableSQL << "interface VARCHAR(255)" << ",";
    createTableSQL << "address VARCHAR(255)" << ",";
    createTableSQL << "backupDate VARCHAR(255)" << ",";
    createTableSQL << "command VARCHAR(255)" << ",";
    createTableSQL << "protocol INTEGER" << ",";
    createTableSQL << "account VARCHAR(255)" << ",";
    createTableSQL << "password VARCHAR(255)" << ",";
    createTableSQL << "privilegedPassword VARCHAR(255)" << ",";
    createTableSQL << "port INTEGER",
    createTableSQL << ");";

    ret = duckdb_query(conn, createTableSQL.str().c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task 表创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_backup_task 表初始化完成..");
}

void MemoryDBHolder::_initObservedTargetTable()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    SPDLOG_INFO("为 observed_target 表重置自增主键序列...");

    // 查询结果
    duckdb_result queryResult;

    std::string dropPrimaryKeySequenceSQL("DROP SEQUENCE IF EXISTS seq_observed_target_id;");
    ret = duckdb_query(conn, dropPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "observed_target 表 自增主键序列 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("为 observed_target 表创建自增主键序列");

    std::string createPrimaryKeySequenceSQL("CREATE SEQUENCE seq_observed_target_id START 1;");
    ret = duckdb_query(conn, createPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "observed_target 表 自增主键序列 创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("重置 t_observed_target 表..");

    std::string dropTableSQL("DROP TABLE IF EXISTS t_observed_target;");
    ret = duckdb_query(conn, dropTableSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_observed_target 表 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    // 执行SQL,创建表
    SPDLOG_INFO("初始化 t_observed_target 表..");

    std::stringstream createTableSQL;
    createTableSQL << "CREATE TABLE t_observed_target";
    createTableSQL << "(";
    createTableSQL << "id INTEGER PRIMARY KEY DEFAULT nextval('seq_observed_target_id')" << ",";
    createTableSQL << "uuid VARCHAR(255)" << ",";
    createTableSQL << "interface VARCHAR(255)" << ",";
    createTableSQL << "address VARCHAR(255)" << ",";
    createTableSQL << "port INTEGER" << ",";
    createTableSQL << "protocol INTEGER,";
    createTableSQL << "responseCode VARCHAR(255)" << ",";
    createTableSQL << "ignoreTsl BOOLEAN" << ",";
    createTableSQL << "requestMethod VARCHAR(255)" << ",";
    createTableSQL << "requestHeader VARCHAR(255)" << ",";
    createTableSQL << "requestBody VARCHAR(255)" << ",";
    createTableSQL << "responseChallenge BOOLEAN" << ",";
    createTableSQL << "userName VARCHAR(255)" << ",";
    createTableSQL << "password VARCHAR(255)" << ",";
    createTableSQL << "databaseName VARCHAR(255)" << ",";
    createTableSQL << "queryStatement VARCHAR(255)" << ",";
    createTableSQL << "matchCondition BOOLEAN" << ",";
    createTableSQL << "keyWords VARCHAR(255)";
    createTableSQL << ");";

    ret = duckdb_query(conn, createTableSQL.str().c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_observed_target 表创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("观测目标表 t_observed_target 初始化完成..");
}

void MemoryDBHolder::batchInsertObservedTargets(const std::vector<ObservedTargetModel>& observedTargets)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_observed_target 表 批量插入数据...");

    // duckdb追加器
    duckdb_appender appender;

    // 创建追加器
    if (duckdb_appender_create(conn, nullptr, "t_observed_target", &appender) == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("duckdb追加器创建失败..");
    }

    // 观测任务数量
    std::size_t observedTargetsSize = observedTargets.size();
    // 获取一批主键值
    std::int64_t* idSeqValues = static_cast<std::int64_t*>(malloc(observedTargetsSize * sizeof(int64_t)));

    // 查询结果
    duckdb_result idSeqQueryResult;

    // idSeqQuerySQL
    std::stringstream idSeqQuerySQL;
    idSeqQuerySQL << "SELECT nextval('seq_observed_target_id') FROM generate_series(" << observedTargetsSize << ");";
    // 查询主键
    duckdb_query(conn, idSeqQuerySQL.str().c_str(), &idSeqQueryResult);
    // 读取主键
    for (std::size_t i = 0; i < observedTargetsSize; i++) {
        idSeqValues[i] = duckdb_value_int64(&idSeqQueryResult, 0, i);
    }

    // 销毁查询
    duckdb_destroy_result(&idSeqQueryResult);

    // 遍历追加
    std::size_t flushRowCount = 0;

    // 遍历行，开始追加
    std::size_t index = 0;
    for (auto it = observedTargets.begin(); it != observedTargets.end(); it++) {
        // 开始行
        duckdb_appender_begin_row(appender);

        // 计算当前probeTask索引值
        index = std::distance(observedTargets.begin(), it);
        duckdb_append_int64(appender, idSeqValues[index]);  // 主键

        duckdb_append_varchar(appender, it->uuid.c_str());
        duckdb_append_varchar(appender, it->interface.c_str());
        duckdb_append_varchar(appender, it->address.c_str());
        duckdb_append_uint16(appender, it->port);
        duckdb_append_uint8(appender, it->protocol);
        duckdb_append_varchar(appender, it->responseCode.c_str());
        duckdb_append_int8(appender, it->ignoreTsl);
        duckdb_append_varchar(appender, it->requestMethod.c_str());
        duckdb_append_varchar(appender, it->requestHeader.c_str());
        duckdb_append_varchar(appender, it->requestBody.c_str());
        duckdb_append_bool(appender, it->responseChallenge);
        duckdb_append_varchar(appender, it->userName.c_str());
        duckdb_append_varchar(appender, it->password.c_str());
        duckdb_append_varchar(appender, it->databaseName.c_str());
        duckdb_append_varchar(appender, it->queryStatement.c_str());
        duckdb_append_bool(appender, it->matchCondition);
        duckdb_append_varchar(appender, it->keyWords.c_str());

        // 结束行
        duckdb_appender_end_row(appender);

        // 每1000条刷新一次
        if (++flushRowCount % 1000 == 0) duckdb_appender_flush(appender);
    }

    // 释放主键序列
    free(idSeqValues);

    // 将缓存刷进数据库
    if (duckdb_appender_close(appender) == DuckDBError) {
        // 获取错误数据
        duckdb_error_data errorData = duckdb_appender_error_data(appender);
        // 错误日志
        SPDLOG_INFO("duckdb追加器插入数据失败..");
        // 销毁追加器。将所有数据写入表
        duckdb_destroy_error_data(&errorData);
        duckdb_appender_destroy(&appender);
        duckdb_disconnect(&conn);
        // 结束执行
        return;
    }

    // 销毁追加器。将所有数据写入表
    duckdb_appender_destroy(&appender);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("向 t_observed_target 表 批量插入数据 成功: {}", observedTargets.size());
}

void MemoryDBHolder::batchRemoveObservedTargetsByInterfaceName(const std::string &interfaceName)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除所有记录
    std::stringstream removeSQL;
    removeSQL << "DELETE FROM t_observed_target WHERE interfaceName='" << interfaceName << "';";

    SPDLOG_DEBUG(removeSQL.str());

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, removeSQL.str().c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_observed_target 表 删除网口所属观测设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_observed_target 表 删除网口所属观测设备完成..");
}

void MemoryDBHolder::batchRemoveObservedTargetsByUUIDs(const std::vector<std::string> &uuids)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除记录
    std::stringstream batchRemoveSQL;
    batchRemoveSQL << "DELETE FROM t_observed_target WHERE uuid IN (";

    for (auto it = uuids.begin(); it != uuids.end(); it++) {
        batchRemoveSQL << "'" << *it << "'" << ",";
    }

    // 转成std::string
    std::string sql = batchRemoveSQL.str();
    // 移除最后一个,
    sql.erase(sql.find_last_of(","), 1);
    // 追加一个;
    sql.append(");");

    SPDLOG_DEBUG(sql);

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, sql.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_observed_target表 批量删除观测设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_observed_target表 批量删除观测设备 完成..");
}


void MemoryDBHolder::batchRemoveAllObservedTargets()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除所有记录
    std::string removeAllSQL("TRUNCATE TABLE t_observed_target");

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, removeAllSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "observed_target 表 删除所有记录 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("observed_target 表 删除所有记录完成..");
}

void MemoryDBHolder::_initDeviceTable()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    SPDLOG_INFO("为 t_device 表重置自增主键序列...");

    std::string dropPrimaryKeySequenceSQL("DROP SEQUENCE IF EXISTS seq_device_id;");

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, dropPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 自增主键序列 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("为 t_device 表创建自增主键序列");

    std::string createPrimaryKeySequenceSQL("CREATE SEQUENCE seq_device_id START 1;");

    // 执行查询
    ret = duckdb_query(conn, createPrimaryKeySequenceSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 自增主键序列 创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    SPDLOG_INFO("重置 t_device 表..");

    std::string dropTableSQL("DROP TABLE IF EXISTS t_device;");

    // 执行查询
    ret = duckdb_query(conn, dropTableSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 重置失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);

    // 执行SQL,创建表
    SPDLOG_INFO("初始化 t_device 表..");

    std::stringstream createTableSQL;
    createTableSQL << "CREATE TABLE t_device";
    createTableSQL << "(";
    createTableSQL << "id INTEGER PRIMARY KEY DEFAULT NEXTVAL('seq_device_id'),";
    createTableSQL << "uuid VARCHAR(255)" << ",";
    createTableSQL << "interface VARCHAR(255)" << ",";
    createTableSQL << "address VARCHAR(255)" << ",";
    createTableSQL << "backupDate VARCHAR(255)" << ",";
    createTableSQL << "command VARCHAR(255)" << ",";
    createTableSQL << "protocol INTEGER" << ",";
    createTableSQL << "account VARCHAR(255)" << ",";
    createTableSQL << "password VARCHAR(255)" << ",";
    createTableSQL << "privilegedPassword VARCHAR(255)" << ",";
    createTableSQL << "port INTEGER",
    createTableSQL << ");";

    // 执行查询
    ret = duckdb_query(conn, createTableSQL.str().c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表创建失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("备份目标表 t_device 初始化完成..");
}

void MemoryDBHolder::batchInsertDevices(const std::vector<DeviceModel>& devices)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_device 表 批量插入数据...");

    // duckdb追加器
    duckdb_appender appender;

    // 创建追加器
    if (duckdb_appender_create(conn, nullptr, "t_device", &appender) == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("duckdb追加器创建失败..");
    }

    // 观测任务数量
    std::size_t devicesSize = devices.size();
    // 获取一批主键值
    std::int64_t* idSeqValues = static_cast<std::int64_t*>(malloc(devicesSize * sizeof(int64_t)));

    // 查询结果
    duckdb_result idSeqQueryResult;

    // idSeqQuerySQL
    std::stringstream idSeqQuerySQL;
    idSeqQuerySQL << "SELECT nextval('seq_device_id') FROM generate_series(" << devicesSize << ");";
    // 查询主键
    duckdb_query(conn, idSeqQuerySQL.str().c_str(), &idSeqQueryResult);
    // 读取主键
    for (std::size_t i = 0; i < devicesSize; i++) {
        idSeqValues[i] = duckdb_value_int64(&idSeqQueryResult, 0, i);
    }

    // 销毁查询
    duckdb_destroy_result(&idSeqQueryResult);

    // 遍历追加
    std::size_t flushRowCount = 0;

    // 遍历行，开始追加
    std::size_t index = 0;
    for (auto it = devices.begin(); it != devices.end(); it++) {
        // 开始行
        duckdb_appender_begin_row(appender);

        // 计算当前probeTask索引值
        index = std::distance(devices.begin(), it);
        duckdb_append_int64(appender, idSeqValues[index]);  // 主键

        duckdb_append_varchar(appender, it->uuid.c_str());
        duckdb_append_varchar(appender, it->interface.c_str());
        duckdb_append_varchar(appender, it->address.c_str());
        duckdb_append_varchar(appender, it->backupDate.c_str());
        duckdb_append_varchar(appender, it->command.c_str());
        duckdb_append_int8(appender, it->protocol);
        duckdb_append_varchar(appender, it->account.c_str());
        duckdb_append_varchar(appender, it->password.c_str());
        duckdb_append_varchar(appender, it->privilegedPassword.c_str());
        duckdb_append_uint64(appender, it->port);

        // 结束行
        duckdb_appender_end_row(appender);

        // 每1000条刷新一次
        if (++flushRowCount % 1000 == 0) duckdb_appender_flush(appender);
    }

    // 释放主键序列
    free(idSeqValues);

    // 将缓存刷进数据库
    if (duckdb_appender_close(appender) == DuckDBError) {
        // 获取错误数据
        duckdb_error_data errorData = duckdb_appender_error_data(appender);
        // 错误日志
        SPDLOG_INFO("duckdb追加器插入数据失败..");
        // 销毁追加器。将所有数据写入表
        duckdb_destroy_error_data(&errorData);
        duckdb_appender_destroy(&appender);
        duckdb_disconnect(&conn);
        // 结束执行
        return;
    }

    // 销毁追加器。将所有数据写入表
    duckdb_appender_destroy(&appender);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("向 t_device 表 批量插入数据 成功: {}", devices.size());
}

void MemoryDBHolder::batchRemoveDevicesByInterfaceName(const std::string &interfaceName)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除所有记录
    std::stringstream removeSQL;
    removeSQL << "DELETE FROM t_device WHERE interfaceName='" << interfaceName << "';";

    SPDLOG_DEBUG(removeSQL.str());

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, removeSQL.str().c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 删除网口所属备份设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_device表 删除网口所属备份设备完成..");
}

void MemoryDBHolder::batchRemoveDevicesByUUIDs(const std::vector<std::string> &uuids)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除记录
    std::stringstream batchRemoveSQL;
    batchRemoveSQL << "DELETE FROM t_device WHERE uuid IN (";

    for (auto it = uuids.begin(); it != uuids.end(); it++) {
        batchRemoveSQL << "'" << *it << "'" << ",";
    }

    // 转成std::string
    std::string sql = batchRemoveSQL.str();
    // 移除最后一个,
    sql.erase(sql.find_last_of(","), 1);
    // 追加一个;
    sql.append(");");

    SPDLOG_INFO(sql);

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, sql.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device表 批量删除备份设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_device表 批量删除备份设备 完成..");
}

void MemoryDBHolder::batchRemoveAllDevices()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除所有记录
    std::string removeAllSQL("TRUNCATE TABLE t_device");

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, removeAllSQL.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 删除所有记录 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_device 表 删除所有记录完成..");
}


MemoryDBHolder &MemoryDBHolder::getInstance()
{
    static MemoryDBHolder instance;
    return instance;
}

MemoryDBHolder::MemoryDBHolder()
{
    // 配置数据库
    duckdb_config config;

    // 创建配置项
    duckdb_state ret = duckdb_create_config(&config);
    if (ret == DuckDBError) {
        duckdb_destroy_config(&config);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库配置项创建失败..");
    }

    // 设定配置项
    duckdb_set_config(config, "access_mode", "AUTOMATIC"); // 数据库的访问模式（AUTOMATIC（自动）、READ_ONLY（只读）或 READ_WRITE（读写）） 默认：AUTOMATIC
    // duckdb_set_config(config, "threads", "4");  // 系统使用的总线程数 默认：CPU 核心数
    duckdb_set_config(config, "max_memory", "3GB");  // 最大内存 默认：RAM 的 80%
    duckdb_set_config(config, "default_order", "DESC");

    // 连接数据库文件 (用进程独占文件)
    ret = duckdb_open_ext(nullptr, &m_db, config, nullptr);
    if (ret == DuckDBError) {
        duckdb_destroy_config(&config);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库打开失败...");
    }

    // 回收配置资源 （配置完配置项就无效了）
    duckdb_destroy_config(&config);
}

std::vector<ObservedTargetModel> MemoryDBHolder::getAllObservedTargets()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, "SELECT * FROM t_observed_target", &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "从 t_observed_target 表获取所有观测设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    // 获取结果集的行数和列数
    idx_t resultRowCount = duckdb_row_count(&queryResult);

    // 从查询结果中构建集合
    std::vector<ObservedTargetModel> observedTargets;
    observedTargets.reserve(resultRowCount);

    // 遍历结果集，组装成模型
    // 遍历行
    for (idx_t row = 0; row < resultRowCount; row++) {
        // 构建观测目标对象
        ObservedTargetModel observedTarget;

        // 获取每个单元格值
        observedTarget.id = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::id), row);
        observedTarget.uuid = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::uuid), row);
        observedTarget.interface = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::interface), row);
        observedTarget.address = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::address), row);
        observedTarget.port = duckdb_value_uint32(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::port), row);
        observedTarget.protocol = duckdb_value_uint32(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::protocol), row);
        observedTarget.responseCode = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::responseCode), row);
        observedTarget.ignoreTsl = duckdb_value_boolean(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::ignoreTsl), row);
        observedTarget.requestMethod = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::requestMethod), row);
        observedTarget.requestHeader = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::requestHeader), row);
        observedTarget.requestBody = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::requestBody), row);
        observedTarget.responseChallenge = duckdb_value_boolean(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::responseChallenge), row);
        observedTarget.userName = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::userName), row);
        observedTarget.password = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::password), row);
        observedTarget.databaseName = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::databaseName), row);
        observedTarget.queryStatement = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::queryStatement), row);
        observedTarget.matchCondition = duckdb_value_boolean(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::matchCondition), row);
        observedTarget.keyWords = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ObservedTargetTableColIdx::keyWords), row);

        // 归集模型数据
        observedTargets.push_back(std::move(observedTarget));
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("从 t_observed_target 表获取所有观测设备 成功: {}", observedTargets.size());

    return observedTargets;
}

std::vector<DeviceModel> MemoryDBHolder::getAllDevices()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, "SELECT * FROM t_device", &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "从 t_device 表获取所有备份设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    // 获取结果集的行数和列数
    idx_t resultRowCount = duckdb_row_count(&queryResult);

    // 从查询结果中构建集合
    std::vector<DeviceModel> devices;
    devices.reserve(resultRowCount);

    // 遍历结果集，组装成模型
    // 遍历行
    for (idx_t row = 0; row < resultRowCount; row++) {
        // 构建观测目标对象
        DeviceModel device;

        // 获取每个单元格值
        device.id = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::id), row);
        device.uuid = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::uuid), row);
        device.interface = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::interface), row);
        device.address = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::address), row);
        device.backupDate = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::backupDate), row);
        device.command = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::command), row);
        device.protocol = duckdb_value_uint8(&queryResult, static_cast<idx_t>(DeviceTableColIdx::protocol), row);
        device.account = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::account), row);
        device.password = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::password), row);
        device.privilegedPassword = duckdb_value_varchar(&queryResult, static_cast<idx_t>(DeviceTableColIdx::privilegedPassword), row);
        device.port = duckdb_value_uint16(&queryResult, static_cast<idx_t>(DeviceTableColIdx::port), row);

        // 归集模型数据
        devices.push_back(std::move(device));
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("从 t_device 表获取所有观测设备 成功..");

    return devices;
}

void MemoryDBHolder::batchInsertProbeTasks(std::span<ProbeTaskModel> probeTasks)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_probe_task 表 批量插入数据...");

    // duckdb追加器
    duckdb_appender appender;

    // 创建追加器
    if (duckdb_appender_create(conn, nullptr, "t_probe_task", &appender) == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("duckdb追加器创建失败..");
    }

    // 观测任务数量
    std::size_t probeTasksSize = probeTasks.size();
    // 获取一批主键值
    std::int64_t* idSeqValues = static_cast<std::int64_t*>(malloc(probeTasksSize * sizeof(int64_t)));

    // 查询结果
    duckdb_result idSeqQueryResult;

    // idSeqQuerySQL
    std::stringstream idSeqQuerySQL;
    idSeqQuerySQL << "SELECT nextval('seq_probe_task_id') FROM generate_series(" << probeTasksSize << ");";
    // 查询主键
    duckdb_query(conn, idSeqQuerySQL.str().c_str(), &idSeqQueryResult);
    // 读取主键
    for (std::size_t i = 0; i < probeTasksSize; i++) {
        idSeqValues[i] = duckdb_value_int64(&idSeqQueryResult, 0, i);
    }

    // 销毁查询
    duckdb_destroy_result(&idSeqQueryResult);

    // 遍历追加
    std::size_t flushRowCount = 0;

    // 遍历行，开始追加
    std::size_t index = 0;
    for (auto it = probeTasks.begin(); it != probeTasks.end(); it++) {
        // 开始行
        duckdb_appender_begin_row(appender);

        // 计算当前probeTask索引值
        index = std::distance(probeTasks.begin(), it);
        duckdb_append_int64(appender, idSeqValues[index]);  // 主键

        duckdb_append_varchar(appender, it->uuid.c_str());
        duckdb_append_varchar(appender, it->interface.c_str());
        duckdb_append_varchar(appender, it->address.c_str());
        duckdb_append_uint16(appender, it->port);
        duckdb_append_uint8(appender, it->protocol);
        duckdb_append_varchar(appender, it->responseCode.c_str());
        duckdb_append_int8(appender, it->ignoreTsl);
        duckdb_append_varchar(appender, it->requestMethod.c_str());
        duckdb_append_varchar(appender, it->requestHeader.c_str());
        duckdb_append_varchar(appender, it->requestBody.c_str());
        duckdb_append_bool(appender, it->responseChallenge);
        duckdb_append_varchar(appender, it->userName.c_str());
        duckdb_append_varchar(appender, it->password.c_str());
        duckdb_append_varchar(appender, it->databaseName.c_str());
        duckdb_append_varchar(appender, it->queryStatement.c_str());
        duckdb_append_bool(appender, it->matchCondition);
        duckdb_append_varchar(appender, it->keyWords.c_str());

        // 结束行
        duckdb_appender_end_row(appender);

        // 每1000条刷新一次
        if (++flushRowCount % 1000 == 0) duckdb_appender_flush(appender);
    }

    // 释放主键序列
    free(idSeqValues);

    // 将缓存刷进数据库
    if (duckdb_appender_close(appender) == DuckDBError) {
        // 获取错误数据
        duckdb_error_data errorData = duckdb_appender_error_data(appender);
        // 错误日志
        SPDLOG_INFO("duckdb追加器插入数据失败..");
        // 销毁追加器。将所有数据写入表
        duckdb_destroy_error_data(&errorData);
        duckdb_appender_destroy(&appender);
        duckdb_disconnect(&conn);
        // 结束执行
        return;
    }

    // 销毁追加器。将所有数据写入表
    duckdb_appender_destroy(&appender);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("向 t_probe_task 表 批量插入数据 成功: {}", probeTasks.size());
}

void MemoryDBHolder::batchInsertBackupTasks(std::span<BackupTaskModel> backupTasks)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_backup_task 表 批量插入数据...");

    // duckdb追加器
    duckdb_appender appender;

    // 创建追加器
    if (duckdb_appender_create(conn, nullptr, "t_backup_task", &appender) == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("duckdb追加器创建失败..");
    }

    // 观测任务数量
    std::size_t backupTasksSize = backupTasks.size();
    // 获取一批主键值
    std::int64_t* idSeqValues = static_cast<std::int64_t*>(malloc(backupTasksSize * sizeof(int64_t)));

    // 查询结果
    duckdb_result idSeqQueryResult;

    // idSeqQuerySQL
    std::stringstream idSeqQuerySQL;
    idSeqQuerySQL << "SELECT nextval('seq_backup_task_id') FROM generate_series(" << backupTasksSize << ");";
    // 查询主键
    duckdb_query(conn, idSeqQuerySQL.str().c_str(), &idSeqQueryResult);
    // 读取主键
    for (std::size_t i = 0; i < backupTasksSize; i++) {
        idSeqValues[i] = duckdb_value_int64(&idSeqQueryResult, 0, i);
    }

    // 销毁查询
    duckdb_destroy_result(&idSeqQueryResult);

    // 遍历追加
    std::size_t flushRowCount = 0;

    // 遍历行，开始追加
    std::size_t index = 0;
    for (auto it = backupTasks.begin(); it != backupTasks.end(); it++) {
        // 开始行
        duckdb_appender_begin_row(appender);

        // 计算当前probeTask索引值
        index = std::distance(backupTasks.begin(), it);
        duckdb_append_int64(appender, idSeqValues[index]);  // 主键

        duckdb_append_varchar(appender, it->uuid.c_str());
        duckdb_append_varchar(appender, it->interface.c_str());
        duckdb_append_varchar(appender, it->address.c_str());
        duckdb_append_varchar(appender, it->backupDate.c_str());
        duckdb_append_varchar(appender, it->command.c_str());
        duckdb_append_int8(appender, it->protocol);
        duckdb_append_varchar(appender, it->account.c_str());
        duckdb_append_varchar(appender, it->password.c_str());
        duckdb_append_varchar(appender, it->privilegedPassword.c_str());
        duckdb_append_uint64(appender, it->port);

        // 结束行
        duckdb_appender_end_row(appender);

        // 每1000条刷新一次
        if (++flushRowCount % 1000 == 0) duckdb_appender_flush(appender);
    }

    // 释放主键序列
    free(idSeqValues);

    // 将缓存刷进数据库
    if (duckdb_appender_close(appender) == DuckDBError) {
        // 获取错误数据
        duckdb_error_data errorData = duckdb_appender_error_data(appender);
        // 错误日志
        SPDLOG_INFO("duckdb追加器插入数据失败..");
        // 销毁追加器。将所有数据写入表
        duckdb_destroy_error_data(&errorData);
        duckdb_appender_destroy(&appender);
        duckdb_disconnect(&conn);
        // 结束执行
        return;
    }

    // 销毁追加器。将所有数据写入表
    duckdb_appender_destroy(&appender);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("向 t_backup_task 表 批量插入数据 成功: {}", backupTasks.size());
}

std::vector<ProbeTaskModel> MemoryDBHolder::getAllProbeTasks()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, "SELECT * FROM t_probe_task", &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "从 t_probe_task 表获取所有观测设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    // 获取结果集的行数和列数
    idx_t resultRowCount = duckdb_row_count(&queryResult);

    // 从查询结果中构建集合
    std::vector<ProbeTaskModel> probeTasks;
    probeTasks.reserve(resultRowCount);

    // 遍历结果集，组装成模型
    // 遍历行
    for (idx_t row = 0; row < resultRowCount; row++) {
        // 构建观测目标对象
        ProbeTaskModel probeTask;

        // 获取每个单元格值
        probeTask.id = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::id), row);
        probeTask.uuid = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::uuid), row);
        probeTask.interface = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::interface), row);
        probeTask.address = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::address), row);
        probeTask.port = duckdb_value_uint32(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::port), row);
        probeTask.protocol = duckdb_value_uint32(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::protocol), row);
        probeTask.responseCode = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::responseCode), row);
        probeTask.ignoreTsl = duckdb_value_boolean(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::ignoreTsl), row);
        probeTask.requestMethod = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::requestMethod), row);
        probeTask.requestHeader = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::requestHeader), row);
        probeTask.requestBody = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::requestBody), row);
        probeTask.responseChallenge = duckdb_value_boolean(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::responseChallenge), row);
        probeTask.userName = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::userName), row);
        probeTask.password = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::password), row);
        probeTask.databaseName = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::databaseName), row);
        probeTask.queryStatement = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::queryStatement), row);
        probeTask.matchCondition = duckdb_value_boolean(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::matchCondition), row);
        probeTask.keyWords = duckdb_value_varchar(&queryResult, static_cast<idx_t>(ProbeTaskTableColIdx::keyWords), row);

        // 归集模型数据
        probeTasks.push_back(std::move(probeTask));
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("从 t_probe_task 表获取所有观测设备 成功: {}", probeTasks.size());

    return probeTasks;
}

void MemoryDBHolder::batchRemoveAllProbeTasks()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, "TRUNCATE TABLE t_probe_task", &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_probe_task 表 删除所有记录 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_probe_task 表 删除所有记录完成..");
}

void MemoryDBHolder::insertObservedTarget(const ObservedTargetModel &target)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_observed_target 表 插入数据...");

    std::stringstream insertSQL;

    insertSQL << "INSERT INTO t_observed_target ";
    insertSQL << "(";
    insertSQL << "uuid" << ",";
    insertSQL << "interface" << ",";
    insertSQL << "address" << ",";
    insertSQL << "port" << ",";
    insertSQL << "protocol" << ",";
    insertSQL << "responseCode" << ",";
    insertSQL << "ignoreTsl" << ",";
    insertSQL << "requestMethod" << ",";
    insertSQL << "requestHeader" << ",";
    insertSQL << "requestBody" << ",";
    insertSQL << "responseChallenge" << ",";
    insertSQL << "userName" << ",";
    insertSQL << "password" << ",";
    insertSQL << "databaseName" << ",";
    insertSQL << "queryStatement" << ",";
    insertSQL << "matchCondition" << ",";
    insertSQL << "keyWords";
    insertSQL << ")";

    insertSQL << " VALUES ";

    insertSQL << "(";
    insertSQL << "'" << target.uuid << "',";
    insertSQL << "'" << target.interface << "',";
    insertSQL << "'" << target.address << "',";
    insertSQL << target.port << ",";
    insertSQL << target.protocol << ",";
    insertSQL << "'" << target.responseCode << "',";
    insertSQL << target.ignoreTsl << ",";
    insertSQL << "'" << target.requestMethod << "',";
    insertSQL << "'" << target.requestHeader << "',";
    insertSQL << "'" << target.requestBody << "',";
    insertSQL << target.responseChallenge << ",";
    insertSQL << "'" << target.userName << "',";
    insertSQL << "'" << target.password << "',";
    insertSQL << "'" << target.databaseName << "',";
    insertSQL << "'" << target.queryStatement << "',";
    insertSQL << target.matchCondition << ",";
    insertSQL << "'" << target.keyWords << "'";
    insertSQL << ");";

    // 转成std::string
    std::string sql = insertSQL.str();
    SPDLOG_INFO(sql);

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, sql.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_observed_target 表 插入失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);
}

void MemoryDBHolder::updateObservedTarget(const ObservedTargetModel &target)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_observed_target 表 更新数据...");

    std::stringstream updateSQL;

    updateSQL << "UPDATE t_observed_target SET ";
    updateSQL << "interface=$1,";
    updateSQL << "address=$2,";
    updateSQL << "port=$3,";
    updateSQL << "protocol=$4,";
    updateSQL << "responseCode=$5,";
    updateSQL << "ignoreTsl=$6,";
    updateSQL << "requestMethod=$7,";
    updateSQL << "requestHeader=$8,";
    updateSQL << "requestBody=$9,";
    updateSQL << "responseChallenge=$10,";
    updateSQL << "userName=$11,";
    updateSQL << "password=$12,";
    updateSQL << "databaseName=$13,";
    updateSQL << "queryStatement=$14,";
    updateSQL << "matchCondition=$15,";
    updateSQL << "keyWords=$16";
    updateSQL << " WHERE ";
    updateSQL << "uuid=$17;";

    // 转成std::string
    std::string sql = updateSQL.str();
    SPDLOG_DEBUG(sql);

    // 预处理对象
    duckdb_prepared_statement stmt;

    // 预处理SQL
    if (duckdb_prepare(conn, sql.c_str(), &stmt) == DuckDBError) {
        std::stringstream ss;
        ss << "t_observed_target 表 预处理updateSQL 失败..";
        duckdb_destroy_prepare(&stmt);
        duckdb_disconnect(&conn);
        throw std::runtime_error(ss.str());
    }

    // 绑定参数
    duckdb_bind_varchar(stmt, 1, target.interface.c_str());
    duckdb_bind_varchar(stmt, 2, target.address.c_str());
    duckdb_bind_uint32(stmt, 3, target.port);
    duckdb_bind_uint32(stmt, 4, target.protocol);
    duckdb_bind_varchar(stmt, 5, target.responseCode.c_str());
    duckdb_bind_boolean(stmt, 6, target.ignoreTsl);
    duckdb_bind_varchar(stmt, 7, target.requestMethod.c_str());
    duckdb_bind_varchar(stmt, 8, target.requestHeader.c_str());
    duckdb_bind_varchar(stmt, 9, target.requestBody.c_str());
    duckdb_bind_boolean(stmt, 10, target.responseChallenge);
    duckdb_bind_varchar(stmt, 11, target.userName.c_str());
    duckdb_bind_varchar(stmt, 12, target.password.c_str());
    duckdb_bind_varchar(stmt, 13, target.databaseName.c_str());
    duckdb_bind_varchar(stmt, 14, target.queryStatement.c_str());
    duckdb_bind_boolean(stmt, 15, target.matchCondition);
    duckdb_bind_varchar(stmt, 16, target.keyWords.c_str());
    duckdb_bind_varchar(stmt, 17, target.uuid.c_str());

    // 查询结果
    duckdb_result queryResult;

    // 执行SQL语句
    ret = duckdb_execute_prepared(stmt, &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_observed_target 表 更新失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_destroy_prepare(&stmt);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_destroy_prepare(&stmt);
    duckdb_disconnect(&conn);

    SPDLOG_DEBUG("t_observed_target 表 更新完成..");
}

void MemoryDBHolder::removeObservedTargetByUUID(std::string_view uuid)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除所有记录
    std::stringstream removeSQL;
    removeSQL << "DELETE FROM t_observed_target WHERE uuid='" << uuid << "';";

    std::string sql = removeSQL.str();
    SPDLOG_DEBUG(sql);

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, sql.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_observed_target 表 删除uuid的观测设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_observed_target 表 删除uuid的观测设备完成..");
}

void MemoryDBHolder::insertDevice(const DeviceModel &device)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_device 表 插入数据...");

    std::stringstream insertSQL;

    insertSQL << "INSERT INTO t_device ";
    insertSQL << "(";
    insertSQL << "uuid" << ",";
    insertSQL << "interface" << ",";
    insertSQL << "address" << ",";
    insertSQL << "backupDate" << ",";
    insertSQL << "command" << ",";
    insertSQL << "protocol" << ",";
    insertSQL << "account" << ",";
    insertSQL << "password" << ",";
    insertSQL << "privilegedPassword" << ",";
    insertSQL << "port";
    insertSQL << ")";

    insertSQL << " VALUES ";

    insertSQL << "(";
    insertSQL << "'" << device.uuid << "',";
    insertSQL << "'" << device.interface << "',";
    insertSQL << "'" << device.address << "',";
    insertSQL << "'" << device.backupDate << "',";
    insertSQL << "'" << device.command << "',";
    insertSQL << device.protocol << ",";
    insertSQL << "'" << device.account << "',";
    insertSQL << "'" << device.password << "',";
    insertSQL << "'" << device.privilegedPassword << "',";
    insertSQL << device.port;
    insertSQL << ");";

    // 转成std::string
    std::string sql = insertSQL.str();
    SPDLOG_DEBUG(sql);

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, sql.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 插入失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);
}

void MemoryDBHolder::updateDevice(const DeviceModel &device)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_device 表 更新数据...");

    std::stringstream updateSQL;

    updateSQL << "UPDATE t_device SET ";
    updateSQL << "interface=$1,";
    updateSQL << "address=$2,";
    updateSQL << "backupDate=$3,";
    updateSQL << "command=$4,";
    updateSQL << "protocol=$5,";
    updateSQL << "account=$6,";
    updateSQL << "password=$7,";
    updateSQL << "privilegedPassword=$8,";
    updateSQL << "port=$9";
    updateSQL << " WHERE ";
    updateSQL << "uuid=$10;";

    // 转成std::string
    std::string sql = updateSQL.str();
    SPDLOG_DEBUG(sql);

    // 预处理对象
    duckdb_prepared_statement stmt;

    // 预处理SQL
    if (duckdb_prepare(conn, sql.c_str(), &stmt) == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 预处理updateSQL 失败..";
        duckdb_destroy_prepare(&stmt);
        duckdb_disconnect(&conn);
        throw std::runtime_error(ss.str());
    }

    // 绑定参数
    duckdb_bind_varchar(stmt, 1, device.interface.c_str());
    duckdb_bind_varchar(stmt, 2, device.address.c_str());
    duckdb_bind_varchar(stmt, 3, device.backupDate.c_str());
    duckdb_bind_varchar(stmt, 4, device.command.c_str());
    duckdb_bind_int8(stmt, 5, device.protocol);
    duckdb_bind_varchar(stmt, 6, device.account.c_str());
    duckdb_bind_varchar(stmt, 7, device.password.c_str());
    duckdb_bind_varchar(stmt, 8, device.privilegedPassword.c_str());
    duckdb_bind_uint16(stmt, 9, device.port);
    duckdb_bind_varchar(stmt, 10, device.uuid.c_str());

    // 查询结果
    duckdb_result queryResult;

    // 执行SQL语句
    ret = duckdb_execute_prepared(stmt, &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 更新失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_destroy_prepare(&stmt);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_destroy_prepare(&stmt);
    duckdb_disconnect(&conn);

    SPDLOG_DEBUG("t_device 表 更新完成..");
}

void MemoryDBHolder::removeDeviceByUUID(std::string_view uuid)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除所有记录
    std::stringstream removeSQL;
    removeSQL << "DELETE FROM t_device WHERE uuid='" << uuid << "';";

    std::string sql = removeSQL.str();
    SPDLOG_DEBUG(sql);

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, sql.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_device 表 删除uuid设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_device 表 删除uuid的观测设备完成..");
}

void MemoryDBHolder::insertBackupTaskResult(std::string_view uuid, std::string_view backupData, std::int64_t backupDate)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_backup_task_result 表 插入数据...");

    std::string insertSQL = "INSERT INTO t_backup_task_result (uuid, backupData, backupDate) VALUES ($1, $2, $3);";

    // 预处理对象
    duckdb_prepared_statement stmt;

    // 预处理SQL
    if (duckdb_prepare(conn, insertSQL.c_str(), &stmt) == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 预处理updateSQL 失败..";
        duckdb_destroy_prepare(&stmt);
        duckdb_disconnect(&conn);
        throw std::runtime_error(ss.str());
    }

    // 绑定参数
    duckdb_bind_varchar(stmt, 1, uuid.data());
    duckdb_bind_varchar(stmt, 2, backupData.data());
    duckdb_bind_int64(stmt, 3, backupDate);

    // 查询结果
    duckdb_result queryResult;

    // 执行SQL语句
    ret = duckdb_execute_prepared(stmt, &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 更新失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_destroy_prepare(&stmt);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_destroy_prepare(&stmt);
    duckdb_disconnect(&conn);
}

void MemoryDBHolder::updateBackupTaskResult(std::string_view uuid, std::string_view backupData, std::int64_t backupDate)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_backup_task_result 表 更新数据...");

    std::stringstream updateSQL;

    updateSQL << "UPDATE t_backup_task_result SET ";
    updateSQL << "backupData=$1,";
    updateSQL << "backupDate=$2";
    updateSQL << " WHERE ";
    updateSQL << "uuid=$3;";

    // 转成std::string
    std::string sql = updateSQL.str();
    SPDLOG_INFO(sql);

    // 预处理对象
    duckdb_prepared_statement stmt;

    // 预处理SQL
    if (duckdb_prepare(conn, sql.c_str(), &stmt) == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 预处理updateSQL 失败..";
        duckdb_destroy_prepare(&stmt);
        duckdb_disconnect(&conn);
        throw std::runtime_error(ss.str());
    }

    // 绑定参数
    duckdb_bind_varchar(stmt, 1, backupData.data());
    duckdb_bind_int64(stmt, 2, backupDate);
    duckdb_bind_varchar(stmt, 3, uuid.data());

    // 查询结果
    duckdb_result queryResult;

    // 执行SQL语句
    ret = duckdb_execute_prepared(stmt, &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 更新失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_destroy_prepare(&stmt);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_destroy_prepare(&stmt);
    duckdb_disconnect(&conn);

    SPDLOG_DEBUG("t_backup_task_result 表 更新完成..");
}

void MemoryDBHolder::batchInsertBackupTaskResults(std::span<BackupTaskResultModel> backupTaskResults)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 执行SQL,创建表
    SPDLOG_INFO("向 t_backup_task_result 表 批量插入数据...");

    std::stringstream batchInsertSQL;

    batchInsertSQL << "INSERT INTO t_backup_task_result ";
    batchInsertSQL << "(";
    batchInsertSQL << "uuid" << ",";
    batchInsertSQL << "backupData" << ",";
    batchInsertSQL << "backupDate";
    batchInsertSQL << ")";

    batchInsertSQL << " VALUES ";

    for (auto it = backupTaskResults.begin(); it != backupTaskResults.end(); it++) {
        batchInsertSQL << "(";
        batchInsertSQL << "'" << it->uuid << "',";
        batchInsertSQL << "'" << it->backupData << "',";
        batchInsertSQL << it->backupDate;
        batchInsertSQL << "),";
    }

    // 转成std::string
    std::string sql = batchInsertSQL.str();

    // 移除最后一个,
    sql.erase(sql.find_last_of(","), 1);
    // 追加一个;
    sql.append(";");

    SPDLOG_DEBUG(sql);

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, sql.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 批量插入失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);
}

std::vector<BackupTaskModel> MemoryDBHolder::getAllBackupTasks()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, "SELECT * FROM t_backup_task", &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "从 t_backup_task 表获取所有观测设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    // 获取结果集的行数和列数
    idx_t resultRowCount = duckdb_row_count(&queryResult);

    // 从查询结果中构建集合
    std::vector<BackupTaskModel> backupTasks;
    backupTasks.reserve(resultRowCount);

    // 遍历结果集，组装成模型
    // 遍历行
    for (idx_t row = 0; row < resultRowCount; row++) {
        // 构建观测目标对象
        BackupTaskModel backupTask;

        // 获取每个单元格值
        backupTask.id = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::id), row);
        backupTask.uuid = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::uuid), row);
        backupTask.interface = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::interface), row);
        backupTask.address = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::address), row);
        backupTask.backupDate = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::backupDate), row);
        backupTask.command = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::command), row);
        backupTask.protocol = duckdb_value_uint8(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::protocol), row);
        backupTask.account = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::account), row);
        backupTask.password = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::password), row);
        backupTask.privilegedPassword = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::privilegedPassword), row);
        backupTask.port = duckdb_value_uint16(&queryResult, static_cast<idx_t>(BackupTaskTableColIdx::port), row);

        // 归集模型数据
        backupTasks.push_back(std::move(backupTask));
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("从 t_backup_task 表获取所有备份设备 成功: {}", backupTasks.size());

    return backupTasks;
}

void MemoryDBHolder::batchRemoveAllBackupTasks()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, "TRUNCATE TABLE t_backup_task", &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task 表 删除所有记录 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_backup_task 表 删除所有记录完成..");
}

void MemoryDBHolder::removeBackupTaskResultByUUID(std::string_view uuid)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 删除所有记录
    std::stringstream removeSQL;
    removeSQL << "DELETE FROM t_backup_task_result WHERE uuid='" << uuid << "';";

    std::string sql = removeSQL.str();
    SPDLOG_DEBUG(sql);

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, sql.c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "t_backup_task_result 表 删除uuid设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("t_backup_task_result 表 删除uuid的观测设备完成..");
}

BackupTaskResultModel MemoryDBHolder::getBackupTaskResultByUUID(std::string_view uuid)
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 查询结果
    duckdb_result queryResult;

    std::stringstream getSQL;
    getSQL << "SELECT * FROM t_backup_task_result";
    getSQL << " WHERE uuid='" << uuid << "';";

    SPDLOG_INFO(getSQL.str());

    // 执行查询
    ret = duckdb_query(conn, getSQL.str().c_str(), &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "从 t_backup_task_result 表获取所有观测设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    // 获取结果集的行数和列数
    // idx_t resultRowCount = duckdb_row_count(&queryResult);

    // 遍历结果集，组装成模型
    // 遍历行
    // 构建观测目标对象
    BackupTaskResultModel backupTaskResult;

    // 获取每个单元格值
    backupTaskResult.id = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskResultTableColIdx::id), 0);
    backupTaskResult.uuid = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskResultTableColIdx::uuid), 0);
    backupTaskResult.backupData = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskResultTableColIdx::backupData), 0);
    backupTaskResult.backupDate = duckdb_value_int64(&queryResult, static_cast<idx_t>(BackupTaskResultTableColIdx::backupDate), 0);

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("从 t_backup_task_result 表获取所有观测设备 成功..");

    return backupTaskResult;
}

std::vector<BackupTaskResultModel> MemoryDBHolder::getAllBackupTaskResults()
{
    // 建立数据库连接，初始化数据表
    duckdb_connection conn;

    // 连接数据库 conn
    duckdb_state ret = duckdb_connect(m_db, &conn);
    if (ret == DuckDBError) {
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error("内存数据库连接失败..");
    }

    // 查询结果
    duckdb_result queryResult;

    // 执行查询
    ret = duckdb_query(conn, "SELECT * FROM t_backup_task_result", &queryResult);
    if (ret == DuckDBError) {
        std::stringstream ss;
        ss << "从 t_backup_task_result 表获取所有设备 失败: " << duckdb_result_error(&queryResult);
        duckdb_destroy_result(&queryResult);
        duckdb_disconnect(&conn);
        duckdb_close(&m_db);
        throw std::runtime_error(ss.str());
    }

    // 获取结果集的行数和列数
    idx_t resultRowCount = duckdb_row_count(&queryResult);

    // 从查询结果中构建集合
    std::vector<BackupTaskResultModel> backupTaskResults;
    backupTaskResults.reserve(resultRowCount);

    // 遍历结果集，组装成模型
    // 遍历行
    for (idx_t row = 0; row < resultRowCount; row++) {
        // 构建观测目标对象
        BackupTaskResultModel backupTaskResult;

        // 获取每个单元格值
        backupTaskResult.id = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskResultTableColIdx::id), row);
        backupTaskResult.uuid = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskResultTableColIdx::uuid), row);
        backupTaskResult.backupData = duckdb_value_varchar(&queryResult, static_cast<idx_t>(BackupTaskResultTableColIdx::backupData), row);
        backupTaskResult.backupDate = duckdb_value_int64(&queryResult, static_cast<idx_t>(BackupTaskResultTableColIdx::backupDate), row);

        // 归集模型数据
        backupTaskResults.push_back(std::move(backupTaskResult));
    }

    duckdb_destroy_result(&queryResult);
    duckdb_disconnect(&conn);

    SPDLOG_INFO("从 t_backup_task_result 表获取所有观测设备 成功..");

    return backupTaskResults;
}

MemoryDBHolder::~MemoryDBHolder()
{
    duckdb_close(&m_db);
    SPDLOG_INFO("内存数据库holder释放完成..");
}

}
